﻿using Dapper;
using System.Data.SqlClient;
using WebApplication1.Entity;
using yunbo.Model.SM;

namespace yunbo.Repositorys.SMRepositorys
{
    public class WLDBRepository
    {
        public string data = "Data Source=121.37.93.71;Initial Catalog=FanXingMES;User ID=sa;Password=geili@2025;";
        public FYwldb GetFYwldb(int PageNumber, int PageSize)
        {

            using (SqlConnection connection = new SqlConnection(data))
            {
                string sql = $"select a.ckw_code,a.wldb_id,a.wldb_code,a.wldb_dblx,wldb_jhdbsj,b.ck_name as rk_name,a.rkw_code,c.ck_name,a.wldb_state,a.wldb_bz from wms_wldb a\r\ninner join wbd_ck b on a.rkw_code=b.ck_code\r\ninner join wbd_ck c on a.ckw_code=c.ck_code\r\nORDER BY  [wldb_id] \r\nOFFSET ({PageNumber}-1)*{PageSize} ROWS\r\nFETCH NEXT {PageSize} ROWS ONLY;";
                List<wldb> wldbs = connection.Query<wldb>(sql).ToList();
                string sql1 = $"select count(*) from wms_wldb";
                int zon = connection.Query<int>(sql1).FirstOrDefault();
                FYwldb fYRole = new FYwldb()
                {
                    wldbs = wldbs,
                    zon = zon,
                };
                return fYRole;
            }
        }
        public int Postwldb(wldb wldb)
        {
            using (SqlConnection connection = new SqlConnection(data))
            {
                string sql = $"insert into [dbo].[wms_wldb]([wldb_code],[rkw_code],[ckw_code],[wldb_jhdbsj],[wldb_dblx],[wldb_state],[ckb_code],[rk_code],[wldb_bz],[create_by],[create_time]) values('{wldb.wldb_code}','{wldb.rkw_code}','{wldb.ckw_code}','{wldb.wldb_jhdbsj}','{wldb.wldb_dblx}',{wldb.wldb_state},'{wldb.ckb_code}','{wldb.rk_code}','{wldb.wldb_bz}',{wldb.create_by},'{wldb.create_time}')";
                int rs = connection.Execute(sql);
                return rs;
            }
        }
        public int Updatewldb(wldb wldb)
        {
            using (SqlConnection connection = new SqlConnection(data))
            {
                string sql = $"Update wms_wldb set [rkw_code]='{wldb.rkw_code}',[wldb_jhdbsj]='{wldb.wldb_jhdbsj}',[wldb_dblx]='{wldb.wldb_dblx}',[wldb_bz]='{wldb.wldb_bz}',[update_by]={wldb.update_by},[update_time]='{wldb.update_time}' where [wldb_id]={wldb.wldb_id}";
                int rs = connection.Execute(sql);
                return rs;
            }
        }
        public int Updatewldbzt(int state,string code,string? ckb_code)
        {
            using (SqlConnection connection = new SqlConnection(data))
            {
                string sql = $"update [dbo].[wms_wldb] set [wldb_state]={state},[ckb_code]='{ckb_code}' where [wldb_code]='{code}'";
                int rs = connection.Execute(sql);
                return rs;
            }
        }
        public int Updatewldbrk(string code, string? rk_code)
        {
            using (SqlConnection connection = new SqlConnection(data))
            {
                string sql = $"update [dbo].[wms_wldb] set [rk_code]='{rk_code}' where [wldb_code]='{code}'";
                int rs = connection.Execute(sql);
                return rs;
            }
        }
        public int Updatewldbrkzt(int state, string code, string? rk_code)
        {
            using (SqlConnection connection = new SqlConnection(data))
            {
                string sql = $"update [dbo].[wms_wldb] set [wldb_state]={state},[rk_code]='{rk_code}' where [wldb_code]='{code}'";
                int rs = connection.Execute(sql);
                return rs;
            }
        }
        public int Updatezt(int state, string code)
        {
            using (SqlConnection connection = new SqlConnection(data))
            {
                string sql = $"update [dbo].[wms_wldb] set [wldb_state]={state} where [wldb_code]='{code}'";
                int rs = connection.Execute(sql);
                return rs;
            }
        }
        public FYwldb GetFYTJwldb(int PageNumber, int PageSize,string? lx)
        {

            using (SqlConnection connection = new SqlConnection(data))
            {
                string sql = $"select a.ckw_code,a.wldb_id,a.wldb_code,a.wldb_dblx,wldb_jhdbsj,b.ck_name as rk_name,a.rkw_code,c.ck_name,a.wldb_state,a.wldb_bz from wms_wldb a\r\n" +
                    $"inner join wbd_ck b on a.rkw_code=b.ck_code\r\n" +
                    $"inner join wbd_ck c on a.ckw_code=c.ck_code\r\n" +
                    $"where wldb_dblx Like '%{lx}%'\r\n" +
                    $"ORDER BY  [wldb_id] \r\n" +
                    $"OFFSET ({PageNumber}-1)*{PageSize} ROWS\r\n" +
                    $"FETCH NEXT {PageSize} ROWS ONLY;";
                List<wldb> wldbs = connection.Query<wldb>(sql).ToList();
                string sql1 = $"select count(*) from wms_wldb where wldb_dblx Like '%{lx}%'";
                int zon = connection.Query<int>(sql1).FirstOrDefault();
                FYwldb fYRole = new FYwldb()
                {
                    wldbs = wldbs,
                    zon = zon,
                };
                return fYRole;
            }
        }
    }
}
